# coding: UTF-8
# Filename : report_phone.py
# Company : imedia
# Author : rose
# -*-coding:utf-8 -*-
'对应report_phone.php文件逻辑'
'挖掘入库stat_mobile_brand、stat_mobile_browser、stat_mobile_sys'

import sys
import utils, log, mysql

#日期设定
yesterday = str(utils.getYesterday())
if len(sys.argv) >= 2:
    yesterday = sys.argv[1]
    
print yesterday

def doDelete():
    del_sql = '''delete from stat_mobile where `date` = %s'''
    ok = mysql.delete(del_sql, (yesterday))

search_brand_sql = '''select tb1.cus_id, tb1.phone_brand, count(tb1.phone_brand)
from 
(
select 
cus_id,phone_brand
from im_log
where `time` >= %s
and `time` <= %s
group by ip
) tb1
group by tb1.cus_id, tb1.phone_brand'''


search_browser_sql = '''select tb1.cus_id, tb1.phone_browser, count(tb1.phone_browser)
from 
(
select 
cus_id,phone_browser
from im_log
where `time` >= %s
and `time` <= %s
group by ip
) tb1
group by tb1.cus_id, tb1.phone_browser'''

search_sys_sql = '''select tb1.cus_id, tb1.phone_sys, count(tb1.phone_sys)
from 
(
select 
cus_id,phone_sys
from im_log
where `time` >= %s
and `time` <= %s
group by ip
) tb1
group by tb1.cus_id, tb1.phone_sys'''

insert_sql = '''insert  into `stat_mobile`
 (`date`, `cus_id`, `stat_type`, `stat_code`, `visit_num`) 
 values (%s, %s, %s, %s, %s)'''

#做挖掘逻辑
def doLogic(stat_type, sql):
    start_time_str = yesterday + ' 00:00:00'
    end_time_str = yesterday + ' 23:59:59'
    format = '%Y-%m-%d %H:%M:%S'
    start_time = utils.strToTimestamp(start_time_str, format)
    end_time = utils.strToTimestamp(end_time_str, format)
    rst = mysql.doSearch(sql, [start_time, end_time])
    #brand挖掘入库逻辑
    for obj in rst:
        param = (yesterday, obj[0], obj[1], stat_type, obj[2])
        mysql.insert(insert_sql, param)

doDelete()
doLogic('brand', search_brand_sql)
doLogic('browser', search_browser_sql)
doLogic('sys', search_sys_sql)
log.info('stat_mobile ' + yesterday + ' 数据挖掘成功')
